package com.ets.Vehicle_DAO;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.ets.NxtNmbr_DAO.NxtNmbrDAO;
import com.ets.NxtNmbr_DAO.NxtNmbrDB;
import com.ets.common.DAOFactory;
import com.ets.common.DateUtils;
import com.ets.database.DB_Pipe;

/******************************************************************************* 
 The <big><b>VehicleDB</b></big> class implements the reader & writer methods of
 the <b>VehicleDAO</b> interface to provide the data access to the
 <big><b>Vehicle</b></big> table
 <br><br>
 @author D.Merkling - 1:33:38 PM - Aug 22, 2011
 <br><br>
 @version 1.2  
*******************************************************************************/ 
public class VehicleDB extends DB_Pipe implements VehicleDAO
  {
   // Declare class variables
   private static NxtNmbrDAO nxtnmbrDAO = null;
		
   public VehicleDB()
	 {
	 }

   /*******************************************************************************
	Vehicle reader methods
   *******************************************************************************/
   
   public boolean existsVehicle (int Stck_No)
	 {
	  String exists = "SELECT Stck_No "     +
                        "FROM vehicle "     +
                       "WHERE Stck_No = ?"; 
	  try
		{
	     DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(exists);
		 ps.setInt(1, Stck_No);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public boolean existsVehicle (String VIN)
	 {
	  String exists = "SELECT VIN "     +
                        "FROM vehicle " +
                       "WHERE VIN = ?"; 
	  try
		{
		 DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(exists);
		 ps.setString(1, VIN);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }
   
   public Vehicle getVehicle (int Stck_No)
	 {
      String sel = "SELECT Stck_No, "        +
                          "VIN, "            +
                          "Typ, "            +
                          "Vndr, "           +
                          "Mdl, "            +
                          "Yr, "             +
                          "Clr, "            +
                          "Descr, "           +
                          "Stat, "           +
                          "Dt_Buy, "         +
                          "Amt_Buy, "        +
                          "Miles_Buy, "      +
                          "User_Buy, "       +
                          "Typ_Warr, "       +
                          "Miles_Warr, "     +
                          "YN_Trade, "       +
                          "Vndr_Trade, "     +
                          "Mdl_Trade, "      +
                          "Yr_Trade, "       +
                          "Dt_Sell, "        +
                          "Amt_Sell, "       +
                          "Miles_Sell, "     +
                          "User_Sell "       +
                     "FROM vehicle "         +
		            "WHERE Stck_No = ?"; 
	  try
		{
		 DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(sel);
	     ps.setInt(1, Stck_No);
	     ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
	        String  VIN        = rs.getString("VIN");
		    String  Typ        = rs.getString("Typ");
	        String  Vndr       = rs.getString("Vndr");
	        String  Mdl        = rs.getString("Mdl");
	        int     Yr         = rs.getInt("Yr");
	        String  Clr        = rs.getString("Clr");
	        String  Descr       = rs.getString("Descr");
	        String  Stat       = rs.getString("Stat");
	        Date    Dt_Buy     = rs.getDate("Dt_Buy");
	        double  Amt_Buy    = rs.getDouble("Amt_Buy");
	        int     Miles_Buy  = rs.getInt("Miles_Buy");
	        String  User_Buy   = rs.getString("User_Buy");
	        String  Typ_Warr   = rs.getString("Typ_Warr");
	        int     Miles_Warr = rs.getInt("Miles_Warr");
	        String  YN_Trade   = rs.getString("YN_Trade");
	        String  Vndr_Trade = rs.getString("Vndr_Trade");
	        String  Mdl_Trade  = rs.getString("Mdl_Trade");
	        int     Yr_Trade   = rs.getInt("Yr_Trade");
	        Date    Dt_Sell    = rs.getDate("Dt_Sell");
	        double  Amt_Sell   = rs.getDouble("Amt_Sell");
	        int     Miles_Sell = rs.getInt("Miles_Sell");
	        String  User_Sell  = rs.getString("User_Sell");
	        Vehicle v          = new Vehicle(Stck_No, VIN, Typ, Vndr, Mdl, Yr, Clr, Descr, Stat,
                                                      Dt_Buy, Amt_Buy, Miles_Buy, User_Buy,
                                                      Typ_Warr, Miles_Warr,
                                                      YN_Trade, Vndr_Trade, Mdl_Trade, Yr_Trade,
                                                      Dt_Sell, Amt_Sell, Miles_Sell, User_Sell);
		    rs.close();
		    ps.close();
		    return v;
	       }
	     else
		   {
		    rs.close();
		    ps.close();
		    return null;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public Vehicle getVehicle (String VIN)
	 {
      String sel = "SELECT Stck_No, "        +
                          "VIN, "            +
                          "Typ, "            +
                          "Vndr, "           +
                          "Mdl, "            +
                          "Yr, "             +
                          "Clr, "            +
                          "Descr, "           +
                          "Stat, "           +
                          "Dt_Buy, "         +
                          "Amt_Buy, "        +
                          "Miles_Buy, "      +
                          "User_Buy, "       +
                          "Typ_Warr, "       +
                          "Miles_Warr, "     +
                          "YN_Trade, "       +
                          "Vndr_Trade, "     +
                          "Mdl_Trade, "      +
                          "Yr_Trade, "       +
                          "Dt_Sell, "        +
                          "Amt_Sell, "       +
                          "Miles_Sell, "     +
                          "User_Sell "       +
                     "FROM vehicle "         +
		            "WHERE VIN = ?"; 
	  try
		{
		 DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(sel);
	     ps.setString(1, VIN);
	     ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    int     Stck_No    = rs.getInt("Stck_No");
		    String  Typ        = rs.getString("Typ");
	        String  Vndr       = rs.getString("Vndr");
	        String  Mdl        = rs.getString("Mdl");
	        int     Yr         = rs.getInt("Yr");
	        String  Clr        = rs.getString("Clr");
	        String  Descr       = rs.getString("Descr");
	        String  Stat       = rs.getString("Stat");
	        Date    Dt_Buy     = rs.getDate("Dt_Buy");
	        double  Amt_Buy    = rs.getDouble("Amt_Buy");
	        int     Miles_Buy  = rs.getInt("Miles_Buy");
	        String  User_Buy   = rs.getString("User_Buy");
	        String  Typ_Warr   = rs.getString("Typ_Warr");
	        int     Miles_Warr = rs.getInt("Miles_Warr");
	        String  YN_Trade   = rs.getString("YN_Trade");
	        String  Vndr_Trade = rs.getString("Vndr_Trade");
	        String  Mdl_Trade  = rs.getString("Mdl_Trade");
	        int     Yr_Trade   = rs.getInt("Yr_Trade");
	        Date    Dt_Sell    = rs.getDate("Dt_Sell");
	        double  Amt_Sell   = rs.getDouble("Amt_Sell");
	        int     Miles_Sell = rs.getInt("Miles_Sell");
	        String  User_Sell  = rs.getString("User_Sell");
	        Vehicle v          = new Vehicle(Stck_No, VIN, Typ, Vndr, Mdl, Yr, Clr, Descr, Stat,
                                                      Dt_Buy, Amt_Buy, Miles_Buy, User_Buy,
                                                      Typ_Warr, Miles_Warr,
                                                      YN_Trade, Vndr_Trade, Mdl_Trade, Yr_Trade,
                                                      Dt_Sell, Amt_Sell, Miles_Sell, User_Sell);
		    rs.close();
		    ps.close();
		    return v;
	       }
	     else
		   {
		    rs.close();
		    ps.close();
		    return null;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }
   
   public boolean getVehicles (ArrayList<Vehicle> vehicles)
	 {
	  String qry = "SELECT Stck_No, "      +
                          "VIN, "          +
                          "Typ, "          +
                          "Vndr, "         +
                          "Mdl, "          +
                          "Yr, "           +
                          "Clr, "          +
                          "Descr, "         +
                          "Stat, "         +
                          "Dt_Buy, "       +
                          "Amt_Buy, "      +
                          "Miles_Buy, "    +
                          "User_Buy, "     +
                          "Typ_Warr, "     +
                          "Miles_Warr, "   +
                          "YN_Trade, "     +
                          "Vndr_Trade, "   +
                          "Mdl_Trade, "    +
                          "Yr_Trade, "     +
                          "Dt_Sell, "      +
                          "Amt_Sell, "     +
                          "Miles_Sell, "   +
                          "User_Sell "     +
 	                 "FROM vehicle "       +
	                "ORDER BY Stck_No ASC";
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(qry);
		 ResultSet         rs = ps.executeQuery();
		 while(rs.next())
		   {
		    int     Stck_No    = rs.getInt("Stck_No");
		    String  VIN        = rs.getString("VIN");
		    String  Typ        = rs.getString("Typ");
		    String  Vndr       = rs.getString("Vndr");
		    String  Mdl        = rs.getString("Mdl");
		    int     Yr         = rs.getInt("Yr");
		    String  Clr        = rs.getString("Clr");
		    String  Descr       = rs.getString("Descr");
		    String  Stat       = rs.getString("Stat");
		    Date    Dt_Buy     = rs.getDate("Dt_Buy");
		    double  Amt_Buy    = rs.getDouble("Amt_Buy");
		    int     Miles_Buy  = rs.getInt("Miles_Buy");
		    String  User_Buy   = rs.getString("User_Buy");
		    String  Typ_Warr   = rs.getString("Typ_Warr");
		    int     Miles_Warr = rs.getInt("Miles_Warr");
		    String  YN_Trade   = rs.getString("YN_Trade");
		    String  Vndr_Trade = rs.getString("Vndr_Trade");
		    String  Mdl_Trade  = rs.getString("Mdl_Trade");
		    int     Yr_Trade   = rs.getInt("Yr_Trade");
		    Date    Dt_Sell    = rs.getDate("Dt_Sell");
		    double  Amt_Sell   = rs.getDouble("Amt_Sell");
		    int     Miles_Sell = rs.getInt("Miles_Sell");
		    String  User_Sell  = rs.getString("User_Sell");
		    Vehicle v          = new Vehicle(Stck_No, VIN, Typ, Vndr, Mdl, Yr, Clr, Descr, Stat,
		    		                                  Dt_Buy, Amt_Buy, Miles_Buy, User_Buy,
		    		                                  Typ_Warr, Miles_Warr,
		    		                                  YN_Trade, Vndr_Trade, Mdl_Trade, Yr_Trade,
		    		                                  Dt_Sell, Amt_Sell, Miles_Sell, User_Sell);
		    vehicles.add(v);
		   }
		 rs.close();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   /*******************************************************************************
	Vehicle writer methods
   *******************************************************************************/
   
   /*******************************************************************************
   When adding a new <big><b>Vehicle/b></big> row to the database, the calling
   class <big><b>DOES NOT</b></big> provide the vehicle ID (Stck_No) value. This
   method is responsible for accessing the NxtNmbr_DAO & retrieving the next
   sequential value for Stck_No.
   *******************************************************************************/
   public boolean addVehicle (Vehicle v)
	 {
	  String ins = "INSERT "                     +
	                 "INTO vehicle(Stck_No, "    +
                                  "VIN, "        +
                                  "Typ, "        +
                                  "Vndr, "       +
                                  "Mdl, "        +
                                  "Yr, "         +
                                  "Clr, "        +
                                  "Descr, "       +
                                  "Stat, "       +
                                  "Dt_Buy, "     +
                                  "Amt_Buy, "    +
                                  "Miles_Buy, "  +
                                  "User_Buy, "   +
                                  "Typ_Warr, "   +
                                  "Miles_Warr, " +
                                  "YN_Trade, "   +
                                  "Vndr_Trade, " +
                                  "Mdl_Trade, "  +
                                  "Yr_Trade, "   +
                                  "Dt_Sell, "    +
                                  "Amt_Sell, "   +
                                  "Miles_Sell, " +
                                  "User_Sell "   +
	                              "TMS_Crtd, "   +
	                              "User_Crtd, "  +
	                              "TMS_Updt, "   +
	                              "User_Updt) "  +
			       "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	  nxtnmbrDAO           = DAOFactory.getNxtNmbrDAO();
   	  NxtNmbrDAO nnDAO     = new NxtNmbrDB();
	  int        newStckNo = nnDAO.getNN("ETS", 1);
	  v.setStckNo(newStckNo);          // Send the new Stck_No value back
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(ins);
	     Timestamp         ts = DateUtils.getTmstmp();
         ps.setInt(1, newStckNo);
         ps.setString(2, v.getVIN());
         ps.setString(3, v.getTyp());
         ps.setString(4, v.getVndr());
         ps.setString(5, v.getMdl());
         ps.setInt(6, v.getYr());
         ps.setString(7, v.getClr());
         ps.setString(8, v.getDescr());
         ps.setString(9, v.getStat());
         ps.setDate(10, v.getDtBuy());
         ps.setDouble(11, v.getAmtBuy());
         ps.setInt(12, v.getMilesBuy());
         ps.setString(13, v.getUserBuy());
         ps.setString(14, v.getTypWarr());
         ps.setInt(15, v.getMilesWarr());
         ps.setString(16, v.getYNTrade());
         ps.setString(17, v.getVndrTrade());
         ps.setString(18, v.getMdlTrade());
         ps.setInt(19, v.getYrTrade());
         ps.setDate(20, v.getDtSell());
         ps.setDouble(21, v.getAmtSell());
         ps.setInt(22, v.getMilesSell());
         ps.setString(23, v.getUserSell());
		 ps.setTimestamp(24, ts);
		 ps.setString(25, "USER0001");
		 ps.setTimestamp(26, ts);
		 ps.setString(27, "USER9999");
		 ps.executeUpdate();
         ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public boolean updateVehicle (Vehicle v)
	 {
	  String upd = "UPDATE vehicle "         +
                      "SET VIN = ?, "        +
                          "Typ = ?, "        +
                          "Vndr = ?, "       +
                          "Mdl = ?, "        +
                          "Yr = ?, "         +
                          "Clr = ?, "        +
                          "Descr = ?, "       +
                          "Stat = ?, "       +
                          "Dt_Buy = ?, "     +
                          "Amt_Buy = ?, "    +
                          "Miles_Buy = ?, "  +
                          "User_Buy = ?, "   +
                          "Typ_Warr = ?, "   +
                          "Miles_Warr = ?, " +
                          "YN_Trade = ?, "   +
                          "Vndr_Trade = ?, " +
                          "Mdl_Trade = ?, "  +
                          "Yr_Trade = ?, "   +
                          "Dt_Sell = ?, "    +
                          "Amt_Sell = ?, "   +
                          "Miles_Sell = ?, " +
                          "User_Sell = ?, "  +
                          "TMS_Updt = ?, "   +
                          "User_Updt = ? "   +
                    "WHERE Stck_No = ?";
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(upd);
	     Timestamp         ts = DateUtils.getTmstmp();
         ps.setString(1, v.getVIN());
         ps.setString(2, v.getTyp());
         ps.setString(3, v.getVndr());
         ps.setString(4, v.getMdl());
         ps.setInt(5, v.getYr());
         ps.setString(6, v.getClr());
         ps.setString(7, v.getDescr());
         ps.setString(8, v.getStat());
         ps.setDate(9, v.getDtBuy());
         ps.setDouble(10, v.getAmtBuy());
         ps.setInt(11, v.getMilesBuy());
         ps.setString(12, v.getUserBuy());
         ps.setString(13, v.getTypWarr());
         ps.setInt(14, v.getMilesWarr());
         ps.setString(15, v.getYNTrade());
         ps.setString(16, v.getVndrTrade());
         ps.setString(17, v.getMdlTrade());
         ps.setInt(18, v.getYrTrade());
         ps.setDate(19, v.getDtSell());
         ps.setDouble(20, v.getAmtSell());
         ps.setInt(21, v.getMilesSell());
         ps.setString(22, v.getUserSell());
         ps.setTimestamp(23, ts);
		 ps.setString(24, "USER8888");
	     ps.setInt(25, v.getStckNo());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public boolean deleteVehicle (Vehicle v)
	 {
	  String del = "DELETE "              +
                     "FROM vehicle "      +
                    "WHERE Stck_No = ?";
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(del);
         ps.setInt(1, v.getStckNo());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }
  }